package com.pearlsoft.webapp.sysmgr.user;

import java.io.UnsupportedEncodingException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.pearlsoft.commons.security.CoderUtil;
import com.pearlsoft.commons.utils.StringUtil;
import com.pearlsoft.webapp.core.annotation.QueryHandlerComponent;
import com.pearlsoft.webapp.core.context.SysParamContext;
import com.pearlsoft.webapp.core.dataset.DataSet;
import com.pearlsoft.webapp.core.dataset.DefaultDataSet;
import com.pearlsoft.webapp.core.dataset.Query;
import com.pearlsoft.webapp.core.web.basehandler.AbsBaseQueryHandler;
import com.pearlsoft.webapp.core.web.session.SessionProfile;
import com.pearlsoft.webapp.core.web.util.HttpUtil;
import com.pearlsoft.webapp.sysmgr.userRole.SysUserRoleLoadHandler;

/**
 * 用户管理类
 * @author YEHH
 * 
 */
@QueryHandlerComponent(name = "SysUserLoadHandler")
public class SysUserLoadHandler extends AbsBaseQueryHandler {
    protected static final Log logger = LogFactory.getLog(SysUserLoadHandler.class);

    /**
     * 根据用户CODE 或者用户信息
     * @param query
     * @param ds
     * @throws Exception
     */
    public void queryUserByCode(Query query, final DataSet ds) throws Exception {
        String userCode = (String) query.get("USER_CODE");
        String sql = "SELECT * FROM FROM SYS_USER WHERE  AND USER_CODE=? ";
        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql, new Object[] { userCode });
        for (Iterator<Map<String, Object>> iter = list.iterator(); iter.hasNext();) {
            Map<String, Object> map = iter.next();
            ds.newRecord();
            Iterator<?> _iterMap = map.entrySet().iterator();
            while (_iterMap.hasNext()) {
                Map.Entry<String, ?> entry = (Map.Entry) _iterMap.next();
                Object key = entry.getKey();
                Object val = entry.getValue();
                ds.setValue(key.toString(), val);
            }

        }
    }

    /**
     * 用户查询
     */
    public void handler(Query query, final DataSet ds) throws Exception {
        JdbcTemplate jdbcTemplate = this.getBean("jdbcTemplate", JdbcTemplate.class);
        final int start = query.getStart();
        final int pageSize = query.getPageSize();
        String totalSql = "SELECT COUNT(USER_ID) FROM SYS_USER WHERE 1=1";
        String _sql = "SELECT A.* FROM SYS_USER A WHERE 1=1 ";
        String userName = (String) query.get("USER_NAME");
        String orgId = (String) query.get("ORG_ID");
        if (StringUtil.isNotEmpty(userName)) {
            totalSql = totalSql + " AND USER_NAME LIKE '%" + userName + "%'";

            _sql += " AND A.USER_NAME LIKE '%" + userName + "%'";
        }
        if (StringUtil.isNotEmpty(orgId)) {
            totalSql = totalSql + " AND ORG_ID ='" + orgId + "'";

            _sql += " AND ORG_ID ='" + orgId + "'";
        }

        String sql = "SELECT B.*,C.ORG_SIMPLE_NAME FROM (" + _sql
                + "   limit ?,?) B LEFT JOIN SYS_ORG C ON B.ORG_ID = C.ORG_ID  ";
        int total = jdbcTemplate.queryForInt(totalSql);
        ds.setTotalCount(total);
        ds.setPageSize(pageSize);
        ds.setStartIndex(start);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, new Object[] { ds.getStartIndex(),
                ds.getPageSize() });
        for (Iterator<Map<String, Object>> iter = list.iterator(); iter.hasNext();) {
            Map<String, Object> map = iter.next();
            ds.newRecord();
            Iterator<?> _iterMap = map.entrySet().iterator();
            while (_iterMap.hasNext()) {
                Map.Entry<String, ?> entry = (Map.Entry) _iterMap.next();
                Object key = entry.getKey();
                Object val = entry.getValue();
                ds.setValue(key.toString(), val);
            }

        }

    }

    public void queryOne(Query query, DataSet ds) throws Exception {
        JdbcTemplate jdbcTemplate = this.getBean("jdbcTemplate", JdbcTemplate.class);
        String id = (String) query.get("objId");
        String sql = "SELECT A.*,B.* FROM SYS_USER A,SYS_ORG B WHERE USER_ID=?  AND A.ORG_ID =B.ORG_ID ";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, new Object[] { id });
        for (Iterator<Map<String, Object>> iter = list.iterator(); iter.hasNext();) {
            Map<String, Object> map = iter.next();
            ds.newRecord();
            Iterator<?> _iterMap = map.entrySet().iterator();
            while (_iterMap.hasNext()) {
                Map.Entry<String, ?> entry = (Map.Entry) _iterMap.next();
                Object key = entry.getKey();
                Object val = entry.getValue();
                ds.setValue(key.toString(), val);
            }

        }
    }

    /**
     * 默认实现用户登入
     */
    public void doLogin(Query query, final DataSet ds) throws Exception {
        JdbcTemplate jdbcTemplate = this.getBean("jdbcTemplate", JdbcTemplate.class);
        final String username = (String) query.get("user_name");
        final String password = (String) query.get("user_password");
        String sql = "SELECT USER_ID,ORG_ID,USER_CODE,USER_NAME,USER_PASSWORD,USER_SEX,"
                + "USER_TEL,USER_EMAIL,USER_AVIDATE,C_STATUS,C_REMARK FROM SYS_USER WHERE C_STATUS='1' AND USER_CODE=? ";
        final SysUserLoadHandler me = this;
        jdbcTemplate.query(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, username);
            }
        }, new ResultSetExtractor<Object>() {
            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    ds.newRecord();
                    String spwd = rs.getString(5);
                    String _password = null;
                    try {
                        _password = new String(CoderUtil.encryptMD5(password.getBytes()), "utf-8");
                    } catch (UnsupportedEncodingException e) {
                        e.printStackTrace();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if (_password.equals(spwd)) {
                        DataSet roleDs = new DefaultDataSet("role_dataset");
                        Query query = new Query();
                        query.add("USER_ID", rs.getString("USER_ID"));
                        try {
                            SysUserRoleLoadHandler roleHandler=new SysUserRoleLoadHandler();
                            roleHandler.setApplicationContext(me.context);
                            roleHandler.queryUserRole(query, roleDs);
                            me.getSession().setAttribute("roles", roleDs.getRecords());
                        } catch (Exception e) {
                            return null;
                        }
                        ds.setValue("user_name", username);
                        ds.setValue("status", "success");
                        logger.info("register current user profile into session");
                        SessionProfile profile = new SessionProfile(username);
                        HttpUtil.setUserProfile(me.getSession(), profile);
                        //
                        me.getSession().setAttribute("USERID", rs.getString(1));
                        me.getSession().setAttribute("ORGID", rs.getString(2));
                        me.getSession().setAttribute("USERCODE", rs.getString(3));
                        me.getSession().setAttribute("USERNAME", rs.getString(4));
                        //
                        String domain = System.getProperty(SysParamContext.SYS_DOMAIN);
                        try {
                            me.setCookie(SessionProfile.COOKIE_USERID, new String(Base64.encodeBase64(rs.getString(1)
                                    .getBytes("utf-8")), "utf-8"), domain);
                            me.setCookie(SessionProfile.COOKIE_USERNAME, new String(Base64.encodeBase64(rs.getString(4)
                                    .getBytes("utf-8")), "utf-8"), domain);
                            me.setCookie(SessionProfile.COOKIE_USERCODE, new String(Base64.encodeBase64(username
                                    .getBytes("utf-8")), "utf-8"), domain);
                            me.setCookie(SessionProfile.COOKIE_STATE, new String(Base64
                                    .encodeBase64(SessionProfile.USER_STATE_LOGIN.getBytes("utf-8")), "utf-8"), domain);
                            //
                            me.setCookie(SessionProfile.COOKIE_ORGID, new String(Base64.encodeBase64(rs.getString(2)
                            			.getBytes("utf-8")), "utf-8"), domain);
                        } catch (UnsupportedEncodingException e) {
                            e.printStackTrace();
                        }
                    } else {
                        ds.setValue("status", "failed");
                    }
                    return rs.getString(2);
                }
                return null;
            }
        });
    }

    public void doLogout(Query query, final DataSet ds) throws Exception {
        this.getSession().invalidate();
    }

    public void advSearch(Query query, DataSet ds) throws Exception {
        // TODO Auto-generated method stub

    }

    public void getEntity(Query query, DataSet ds) throws Exception {
        // TODO Auto-generated method stub

    }

    public void quickSearch(Query query, DataSet ds) throws Exception {
        JdbcTemplate jdbcTemplate = this.getBean("jdbcTemplate", JdbcTemplate.class);
        final int start = query.getStart();
        final int pageSize = query.getPageSize();
        String searchValue = (String) query.get("searchValue");
        String totalSql = "SELECT COUNT(USER_ID) FROM SYS_USER WHERE 1=1";
        String sql = "SELECT * FROM SYS_USER ";
        if (!StringUtils.isBlank(searchValue)) {
            sql = sql + " WHERE ";
            sql = sql + "USER_CODE LIKE '%'" + searchValue + "'%' ";
            sql = sql + " OR USER_NAME LIKE '%'" + searchValue + "'%' ";
            sql = sql + " OR C_REMARK LIKE '%'" + searchValue + "'%' ";

            totalSql = totalSql + " WHERE ";
            totalSql = totalSql + "USER_CODE LIKE '%'" + searchValue + "'%' ";
            totalSql = totalSql + " OR USER_NAME LIKE '%'" + searchValue + "'%' ";
            totalSql = totalSql + " OR C_REMARK LIKE '%'" + searchValue + "'%' ";

        }

        String sql2 = "SELECT B.*,C.ORG_SIMPLE_NAME FROM (" + sql
                + "   limit ?,?) B LEFT JOIN SYS_ORG C ON B.ORG_ID = C.ORG_ID  ";
        int total = jdbcTemplate.queryForInt(totalSql);
        ds.setTotalCount(total);
        ds.setPageSize(pageSize);
        ds.setStartIndex(start);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql2, new Object[] { ds.getStartIndex(),
                ds.getPageSize() });
        for (Iterator<Map<String, Object>> iter = list.iterator(); iter.hasNext();) {
            Map<String, Object> map = iter.next();
            ds.newRecord();
            Iterator<?> _iterMap = map.entrySet().iterator();
            while (_iterMap.hasNext()) {
                Map.Entry<String, ?> entry = (Map.Entry) _iterMap.next();
                Object key = entry.getKey();
                Object val = entry.getValue();
                ds.setValue(key.toString(), val);
            }

        }
    }
    
    public void getUserInfoByName(Query query, DataSet ds) throws Exception {
		String userName =(String)query.get("USER_NAME");
		final int start = query.getStart();
		final int pageSize = query.getPageSize();
		
		String sql="SELECT	* FROM SYS_USER Where C_STATUS='1' ";
		String countSql="SELECT	count(*) FROM SYS_USER Where C_STATUS='1' ";
			
		if(StringUtils.isNotEmpty(userName)){
			sql = sql +" AND USER_NAME like '%"+userName+"%' ";
			countSql = countSql +" AND USER_NAME like '%"+userName+"%' ";
		}
		int total = getJdbcTemplate().queryForInt(countSql);
		ds.setTotalCount(total);
		ds.setPageSize(pageSize);
		ds.setStartIndex(start);
		
		sql =sql+ " limit ?,?";
		List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql,new Object[] {ds.getStartIndex(),ds.getPageSize() });
		for (Iterator<Map<String, Object>> iter = list.iterator(); iter.hasNext();) {
			Map<String, Object> map = iter.next();
			ds.newRecord();
			Iterator<?> _iterMap = map.entrySet().iterator();
			while(_iterMap.hasNext()){
				 Map.Entry<String,?> entry = (Map.Entry) _iterMap.next();
				 Object key = entry.getKey();
				 Object val = entry.getValue();
				ds.setValue(key.toString(), val);
			}
		}
	}
}
